/*==============================================================*/
/* DBMS name:      Microsoft SQL Server 2008                    */
/* Created on:     3/8/2014 10:30:59 AM                         */
/*==============================================================*/


if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('chucvu_tacgia') and o.name = 'fk_chucvu_t_chucvu_ta_chucvu')
alter table chucvu_tacgia
   drop constraint fk_chucvu_t_chucvu_ta_chucvu
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('chucvu_tacgia') and o.name = 'fk_chucvu_t_chucvu_ta_tacgia')
alter table chucvu_tacgia
   drop constraint fk_chucvu_t_chucvu_ta_tacgia
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('sach_thanhvien') and o.name = 'fk_sach_tha_sach_than_sach')
alter table sach_thanhvien
   drop constraint fk_sach_tha_sach_than_sach
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('sach_thanhvien') and o.name = 'fk_sach_tha_sach_than_thanhvie')
alter table sach_thanhvien
   drop constraint fk_sach_tha_sach_than_thanhvie
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('tacgia_sach') and o.name = 'fk_tacgia_s_tacgia_sa_tacgia')
alter table tacgia_sach
   drop constraint fk_tacgia_s_tacgia_sa_tacgia
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('tacgia_sach') and o.name = 'fk_tacgia_s_tacgia_sa_sach')
alter table tacgia_sach
   drop constraint fk_tacgia_s_tacgia_sa_sach
go

if exists (select 1
            from  sysobjects
           where  id = object_id('chucvu')
            and   type = 'U')
   drop table chucvu
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('chucvu_tacgia')
            and   name  = 'chucvu_tacgia2_fk'
            and   indid > 0
            and   indid < 255)
   drop index chucvu_tacgia.chucvu_tacgia2_fk
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('chucvu_tacgia')
            and   name  = 'chucvu_tacgia_fk'
            and   indid > 0
            and   indid < 255)
   drop index chucvu_tacgia.chucvu_tacgia_fk
go

if exists (select 1
            from  sysobjects
           where  id = object_id('chucvu_tacgia')
            and   type = 'U')
   drop table chucvu_tacgia
go

if exists (select 1
            from  sysobjects
           where  id = object_id('quanly')
            and   type = 'U')
   drop table quanly
go

if exists (select 1
            from  sysobjects
           where  id = object_id('sach')
            and   type = 'U')
   drop table sach
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('sach_thanhvien')
            and   name  = 'sach_thanhvien2_fk'
            and   indid > 0
            and   indid < 255)
   drop index sach_thanhvien.sach_thanhvien2_fk
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('sach_thanhvien')
            and   name  = 'sach_thanhvien_fk'
            and   indid > 0
            and   indid < 255)
   drop index sach_thanhvien.sach_thanhvien_fk
go

if exists (select 1
            from  sysobjects
           where  id = object_id('sach_thanhvien')
            and   type = 'U')
   drop table sach_thanhvien
go

if exists (select 1
            from  sysobjects
           where  id = object_id('tacgia')
            and   type = 'U')
   drop table tacgia
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('tacgia_sach')
            and   name  = 'tacgia_sach2_fk'
            and   indid > 0
            and   indid < 255)
   drop index tacgia_sach.tacgia_sach2_fk
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('tacgia_sach')
            and   name  = 'tacgia_sach_fk'
            and   indid > 0
            and   indid < 255)
   drop index tacgia_sach.tacgia_sach_fk
go

if exists (select 1
            from  sysobjects
           where  id = object_id('tacgia_sach')
            and   type = 'U')
   drop table tacgia_sach
go

if exists (select 1
            from  sysobjects
           where  id = object_id('thanhvien')
            and   type = 'U')
   drop table thanhvien
go

/*==============================================================*/
/* Table: chucvu                                                */
/*==============================================================*/
create table chucvu (
   chucvu_id            int                  not null,
   tenchucvu            varchar(255)         not null,
   constraint pk_chucvu primary key nonclustered (chucvu_id)
)
go

/*==============================================================*/
/* Table: chucvu_tacgia                                         */
/*==============================================================*/
create table chucvu_tacgia (
   chucvu_id            int                  not null,
   matacgia             varchar(255)         not null,
   constraint pk_chucvu_tacgia primary key (chucvu_id, matacgia)
)
go

/*==============================================================*/
/* Index: chucvu_tacgia_fk                                      */
/*==============================================================*/
create index chucvu_tacgia_fk on chucvu_tacgia (
chucvu_id asc
)
go

/*==============================================================*/
/* Index: chucvu_tacgia2_fk                                     */
/*==============================================================*/
create index chucvu_tacgia2_fk on chucvu_tacgia (
matacgia asc
)
go

/*==============================================================*/
/* Table: quanly                                                */
/*==============================================================*/
create table quanly (
   maquanly             varchar(255)         not null,
   dangnhapquanly       varchar(255)         not null,
   matkhauquanly        varchar(255)         not null,
   hotenquanly          varchar(255)         not null,
   ngaysinhquanly       datetime             not null,
   trangthaiquanly      bit                  not null,
   constraint pk_quanly primary key nonclustered (maquanly)
)
go

/*==============================================================*/
/* Table: sach                                                  */
/*==============================================================*/
create table sach (
   masach               varchar(255)         not null,
   tensach              varchar(255)         not null,
   namxuatban           datetime             not null,
   nhaxuatban           varchar(255)         not null,
   constraint pk_sach primary key nonclustered (masach)
)
go

/*==============================================================*/
/* Table: sach_thanhvien                                        */
/*==============================================================*/
create table sach_thanhvien (
   masach               varchar(255)         not null,
   mathanhvien          varchar(255)         not null,
   constraint pk_sach_thanhvien primary key (masach, mathanhvien)
)
go

/*==============================================================*/
/* Index: sach_thanhvien_fk                                     */
/*==============================================================*/
create index sach_thanhvien_fk on sach_thanhvien (
masach asc
)
go

/*==============================================================*/
/* Index: sach_thanhvien2_fk                                    */
/*==============================================================*/
create index sach_thanhvien2_fk on sach_thanhvien (
mathanhvien asc
)
go

/*==============================================================*/
/* Table: tacgia                                                */
/*==============================================================*/
create table tacgia (
   matacgia             varchar(255)         not null,
   tentacgia            varchar(255)         not null,
   constraint pk_tacgia primary key nonclustered (matacgia)
)
go

/*==============================================================*/
/* Table: tacgia_sach                                           */
/*==============================================================*/
create table tacgia_sach (
   matacgia             varchar(255)         not null,
   masach               varchar(255)         not null,
   constraint pk_tacgia_sach primary key (matacgia, masach)
)
go

/*==============================================================*/
/* Index: tacgia_sach_fk                                        */
/*==============================================================*/
create index tacgia_sach_fk on tacgia_sach (
matacgia asc
)
go

/*==============================================================*/
/* Index: tacgia_sach2_fk                                       */
/*==============================================================*/
create index tacgia_sach2_fk on tacgia_sach (
masach asc
)
go

/*==============================================================*/
/* Table: thanhvien                                             */
/*==============================================================*/
create table thanhvien (
   mathanhvien          varchar(255)         not null,
   dangnhapthanhvien    varchar(255)         not null,
   matkhauthanhvien     varchar(255)         not null,
   hotenthanhvien       varchar(255)         not null,
   ngaysinhthanhvien    datetime             not null,
   lopthanhvien         varchar(255)         not null,
   donvithanhvien       varchar(255)         not null,
   trangthaithanhvien   bit                  not null,
   constraint pk_thanhvien primary key nonclustered (mathanhvien)
)
go

alter table chucvu_tacgia
   add constraint fk_chucvu_t_chucvu_ta_chucvu foreign key (chucvu_id)
      references chucvu (chucvu_id)
go

alter table chucvu_tacgia
   add constraint fk_chucvu_t_chucvu_ta_tacgia foreign key (matacgia)
      references tacgia (matacgia)
go

alter table sach_thanhvien
   add constraint fk_sach_tha_sach_than_sach foreign key (masach)
      references sach (masach)
go

alter table sach_thanhvien
   add constraint fk_sach_tha_sach_than_thanhvie foreign key (mathanhvien)
      references thanhvien (mathanhvien)
go

alter table tacgia_sach
   add constraint fk_tacgia_s_tacgia_sa_tacgia foreign key (matacgia)
      references tacgia (matacgia)
go

alter table tacgia_sach
   add constraint fk_tacgia_s_tacgia_sa_sach foreign key (masach)
      references sach (masach)
go

